﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Configuration;
using System.Data.Common;
using MySql.Data.Common;
using MySql.Data.MySqlClient;

namespace CRMFramework
{
    public static class DBhelper
    {
        //props connect string
        public static string DBConnectionString { get; set; }

        static DBhelper()
        {
            DBConnectionString = ConfigurationManager.ConnectionStrings["CrmConn"].ToString();
        }

        #region  ExecuteNonQuery and ExecuteNonQuerySP

        //Excute database command string
        public static void ExecuteNonQuery(string commandString)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(commandString, cn as MySqlConnection);

            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public static string ExecuteScalar(string commandString)
        {
            String strValue = string.Empty;
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(commandString, cn as MySqlConnection);
           
            try
            {
                cn.Open();
                var objVal = cmd.ExecuteScalar();
                if (objVal != null)
                    strValue = objVal.ToString();
                cn.Close();
                return strValue;
            }
            catch (Exception)
            {
                return strValue;
            }
        }

        public static string ExecuteScalar(string commandString, DbParameter[] commandParameters)
        {
            String strValue = string.Empty;
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(commandString, cn as MySqlConnection);
            try
            {
                foreach (DbParameter parm in commandParameters)
                {
                    cmd.Parameters.Add(parm);
                }
                cn.Open();
                var objVal = cmd.ExecuteScalar();
                if (objVal != null)
                    strValue = objVal.ToString();
                cn.Close();
                return strValue;
            }
            catch (Exception)
            {
                return strValue;
            }
        }
        public static void ExecuteNonQuery(string commandString, DbParameter[] commandParameters)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(commandString, cn as MySqlConnection);

            foreach (DbParameter parm in commandParameters)
            {
                cmd.Parameters.Add(parm);
            }

            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        public static void ExecuteNonQuery(string commandString, List<List<string>> sqlparamters)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(commandString, cn as MySqlConnection);

            foreach (List<string> lst in sqlparamters)
            {
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = lst[0];
                param.Value = lst[1];
                cmd.Parameters.Add(param);
            }

            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        //Excute database StoreProcedure ,no output 
        public static void ExecuteNonQuerySP(string spName)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            MySqlCommand cmd = new MySqlCommand(spName, cn as MySqlConnection);
            cmd.CommandType = CommandType.StoredProcedure; //StoredProcedure

            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        //Over load include Param
        public static void ExecuteNonQuerySP(string spName, DbParameter[] commandParameters)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(spName, cn as MySqlConnection);
            cmd.CommandType = CommandType.StoredProcedure; //StoredProcedure

            foreach (DbParameter parm in commandParameters)
            {
                cmd.Parameters.Add(parm);
            }

            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        #endregion

        #region ExecuteDataset and ExecuteDatasetSP

        //Excute database command string returen dataset
        public static DataSet ExecuteDataset(string commandString)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(commandString, cn as MySqlConnection);
            cmd.CommandTimeout = 0;

            DbDataAdapter da = new MySqlDataAdapter();
            DataSet ds = new DataSet();

            cn.Open();
            da.SelectCommand = cmd;
            da.Fill(ds);
            cn.Close();
            return ds;
        }

        /// <summary>
        /// Excute database command string returen dataset and output total row amount by Wade 20140227
        /// </summary>
        /// <param name="commandString"></param>
        /// <param name="totalAmount"></param>
        /// <returns></returns>
        public static DataSet ExecuteDatasetWithTotalAmount(string commandString, out int totalAmount)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(commandString, cn as MySqlConnection);
            cmd.CommandTimeout = 0;

            DbDataAdapter da = new MySqlDataAdapter();
            DataSet ds = new DataSet();

            cn.Open();
            da.SelectCommand = cmd;
            da.Fill(ds);
            totalAmount = GetToalAmount(cn,da);
            cn.Close();
            return ds;
        }
        /// <summary>
        /// Get total amount of rows -- by Wade
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="da"></param>
        /// <returns></returns>
        private static int GetToalAmount(DbConnection cn, DbDataAdapter da)
        {
            int total = 0;
            DbCommand cmd = new MySqlCommand("SELECT FOUND_ROWS() AS TotalAmount;", cn as MySqlConnection);
            cmd.CommandTimeout = 0;

            DataSet ds = new DataSet();
            da.SelectCommand = cmd;
            da.Fill(ds);
            total=int.Parse(ds.Tables[0].Rows[0]["TotalAmount"].ToString());
            return total;
        }
        //End Excute database command string returen dataset and output total row amount by Wade 20140227
        //Wade coding Return total Amount after Excute database command string returen dataset 
        public static Int32 CountAfterExecuteDataset(string commandString)
        {
            Int32 totalAmount = 0;
            commandString = commandString.Replace("*", "count(*)");
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(commandString, cn as MySqlConnection);
            cmd.CommandTimeout = 0;

            cn.Open();
            totalAmount = Convert.ToInt32(cmd.ExecuteScalar());
            cn.Close();
            return totalAmount;
        }


        public static DataSet ExecuteDataset(string commandString, DbParameter[] commandParameters)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(commandString, cn as MySqlConnection);

            foreach (DbParameter parm in commandParameters)
            {
                cmd.Parameters.Add(parm);
            }

            DbDataAdapter da = new MySqlDataAdapter();
            DataSet ds = new DataSet();

            cn.Open();
            da.SelectCommand = cmd;
            da.Fill(ds);
            cn.Close();
            return ds;
        }

        public static DataTable ExecuteDataTable(string commandString)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(commandString, cn as MySqlConnection);

            DbDataAdapter da = new MySqlDataAdapter();
            DataTable dt = new DataTable();

            cn.Open();
            da.SelectCommand = cmd;
            da.Fill(dt);
            cn.Close();
            return dt;
        }
        public static DataTable ExecuteDataTable(string commandString, List<List<string>> sqlparamters)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(commandString, cn as MySqlConnection);

            foreach (List<string> lst in sqlparamters)
            {
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = lst[0];
                param.Value = lst[1];
                cmd.Parameters.Add(param);
            }

            DbDataAdapter da = new MySqlDataAdapter();
            DataTable dt = new DataTable();
            cn.Open();
            da.SelectCommand = cmd;
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        //Excute sql store_Procedure without  param and return dataset
        public static DataSet ExecuteDatasetSP(string spName)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(spName, cn as MySqlConnection);
            cmd.CommandType = CommandType.StoredProcedure; //StoredProcedure

            DbDataAdapter da = new MySqlDataAdapter();
            DataSet ds = new DataSet();

            cn.Open();
            da.SelectCommand = cmd;
            da.Fill(ds);
            cn.Close();
            return ds;
        }

        //over load Excute sql store_Procedure with param and return dataset
        public static DataSet ExecuteDatasetSP(string spName, DbParameter[] commandParameters)
        {
            DbConnection cn = new MySqlConnection(DBConnectionString);
            DbCommand cmd = new MySqlCommand(spName, cn as MySqlConnection);
            cmd.CommandType = CommandType.StoredProcedure; //StoredProcedure

            foreach (DbParameter parm in commandParameters)
            {
                cmd.Parameters.Add(parm);
            }

            DbDataAdapter da = new MySqlDataAdapter();
            DataSet ds = new DataSet();

            cn.Open();
            da.SelectCommand = cmd;
            da.Fill(ds);
            cn.Close();
            return ds;
        }

        #endregion

        #region SetParameter

        //Set param and it's value to dbparameter
        public static DbParameter SetParameter(string name, object value)
        {
            DbParameter dataParameter = new MySqlParameter();
            dataParameter.ParameterName = name;
            dataParameter.Value = value;
            return dataParameter;
        }

        //Set parameter with name,datatype and direction for input
        public static DbParameter SetParameter(string name, DbType dbType, ParameterDirection direction)
        {

            MySqlParameter dataParameter = new MySqlParameter();
            dataParameter.ParameterName = name;
            dataParameter.DbType = dbType;
            dataParameter.Direction = direction;

            //if (dataParameter is MySqlParameter)
            //{
            //    DbParameter dbDataParameter = (MySqlParameter)dataParameter;
            //}
            return dataParameter;
        }

        //Set param for output 
        public static DbParameter SetParameter(string name, ParameterDirection direction)
        {
            DbParameter dataParameter = new MySqlParameter();
            dataParameter.ParameterName = name;
            dataParameter.Direction = direction;

            //if (dataParameter is DbParameter)
            //{
            //    DbParameter dbDataParameter = (MySqlParameter)dataParameter;
            //}
            return dataParameter;
        }

        //set parameter for appdetails update
        public static DbParameter SetParameterForApp(string name, object value)
        {
            DbParameter dataParameter = new MySqlParameter();
            dataParameter.ParameterName = name;
            dataParameter.Value = value;
            return dataParameter;
        }



        #endregion
    }
}